package cn.liu.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.LinkedList;

/**
 */
public class ExcelParser {

    public static void main(String[] args) {
        String path = "C:\\Users\\LIU\\Desktop\\地质力学\\玫瑰花图data - 副本.xls";  // excel文件绝对路径
        LinkedList<Row> rows = parseExcel(path);
        for (Row row : rows) {
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Object stringCellValue = getCellValue(row, j);
                System.out.print(stringCellValue + "\t");
            }
            System.out.println();
        }
//        updateRows(rows);
//        // 新建一个sheet，将修改后的Rows写入该sheet
//        writeRows(rows, path);
    }

    // 解析excel获取Rows
    private static LinkedList<Row> parseExcel(String path) {
        System.out.println("正在解析excel...");
        LinkedList<Row> res = new LinkedList<>();
        try {
            String fileExtension = getFileExtension(path);
            // 读取Excel文件
            FileInputStream fis = new FileInputStream(path);
            Workbook workbook = "xls".equals(fileExtension) ? new HSSFWorkbook(fis) : new XSSFWorkbook(fis);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            int rowNum = datatypeSheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rowNum; i++) {
                res.add(datatypeSheet.getRow(i));
            }
            // 关闭文件流
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return res;
    }
    public static String getFileExtension(String path){
        File file = new File(path);

        // 1. 获取文件名
        String fileName = file.getName();

        // 2. 查找最后一个点号的位置
        int lastIndexOfDot = fileName.lastIndexOf(".");

        // 3. 判断并截取后缀名
        String fileExtension = "";
        if (lastIndexOfDot > 0) { // 确保点号存在且不在文件名开头（如 .hiddenfile）
            fileExtension = fileName.substring(lastIndexOfDot + 1);
        }

        // 转换为小写，便于统一处理（可选）
        fileExtension = fileExtension.toLowerCase();
        System.out.println("文件后缀名是: " + fileExtension); // 输出: pdf
        return fileExtension;

    }

    /**
     * 获取单元格值
     *
     * @param row    获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public static Object getCellValue(Row row, int column) {
        if (row == null) {
            return row;
        }
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (null != cell) {
                if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA) {
                    val = cell.getNumericCellValue();
                    if (DateUtil.isCellDateFormatted(cell)) {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                    } else {
                        if ((Double) val % 1 != 0) {
                            val = new BigDecimal(val.toString());
                        } else {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                } else if (cell.getCellType() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellType() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellType() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }

            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }

}
